package com.ypn.service.Impl;

import com.ypn.controller.Constants;
import com.ypn.dao.impl.MyHouseDaoImpl;
import com.ypn.dao.impl.MydjDaoImpl;
import com.ypn.entity.MyHouse;
import com.ypn.entity.Mydj;
import com.ypn.service.ServiceDao;
import com.ypn.until.JdbcUntils;
import com.ypn.until.NumberUntil;
import com.ypn.until.Pages;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author ypn
 * @date 2020/8/19
 * @motto I raise pigs in your field
 * @desc  出租费用收入查询
 */
public class MyDjServiceImpl1  {
    MydjDaoImpl mydj = new MydjDaoImpl();
    /**
     * 筛选查询
     *
     * @param objects
     * @param map
     * @return
     * @throws InvocationTargetException
     * @throws SQLException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public List<Mydj> screen(Object[] objects, Map<String, Object> map) throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException {
        List<Mydj> list = new ArrayList<>();
        String sql ="SELECT mydj.MID,HADDRESS,HFH,CNAME,CTEL,mydj.MDATE,MYJ,MYZJ,MFLAG,ENAME\n" +
                "from mydj,mycus,myemp,myhouse,myarea,mysort\n" +
                "where mydj.EID=myemp.EID AND mydj.CID=mycus.CID AND mydj.HID=myhouse.HID AND myhouse.AID=myarea.AID AND myhouse.SID=mysort.SID";
        String countSql = "SELECT count(*) " +
                "from mydj,mycus,myemp,myhouse,myarea,mysort\n" +
                "where mydj.EID=myemp.EID AND mydj.CID=mycus.CID AND mydj.HID=myhouse.HID AND myhouse.AID=myarea.AID AND myhouse.SID=mysort.SID";
        StringBuffer sb = new StringBuffer(sql);
        StringBuffer sb1 = new StringBuffer(countSql);
        List list1 = new ArrayList();
        Integer aid = null;
        Integer sid = null;
        if (objects != null) {
            aid = (Integer) objects[0];
            sid = (Integer) objects[1];
            if (objects.length > 0 && objects != null) {
                //当房屋类型有条件
                if (aid != 0) {
                    sb.append(" AND myhouse.AID=? ");
                    sb1.append(" AND myhouse.AID=" + aid);
                    list1.add("aid," + aid);
                }
                //当出租房屋区域有条件
                if (sid != 0) {
                    sb.append(" AND myhouse.SID =? ");
                    sb1.append(" AND myhouse.SID =" + sid);
                    list1.add("sid," + sid);
                }
            }
        }
        int count = mydj.queryCount(sb1.toString(),null);
        System.out.println("count(*)"+count);
        System.out.println(sb1.toString());
        //分页参数
        int current = NumberUntil.getIntegerValue(map.get("current"), 1);
        Pages pages = new Pages();
        int countPage = pages.getAllPages(count, Constants.PAGE_SIZE_DEFAULT);
        //上一页
        int upIndex = pages.getUp(current);
        //下一页
        int nextIndex = pages.getNext(current, countPage);

//获取分页参数
        if (map.size() != 0) {
            Object start = map.get("start");
            Object PageSize = map.get("PageSize");
            if (start != null && PageSize != null) {
                sb.append(" limit " + start + "," + PageSize);
            }
        }
        System.out.println(sb);
        Connection conn = null;
        PreparedStatement sta = null;
        ResultSet rs = null;
        conn = JdbcUntils.getConn();
        sta = conn.prepareStatement(sb.toString());

        //给对应的占位符赋值
        if (list1.size() > 0 && list1 != null) {
            for (int i = 0; i < list1.size(); i++) {
                String str = (String) list1.get(i);
                String[] strings = str.split(",");
                sta.setObject(i + 1, strings[1]);
                System.out.println(map.get(i));
            }
        }
        rs = sta.executeQuery();
        while (rs.next()) {
            Mydj mydj = new Mydj();
            mydj.setMid(rs.getLong("mydj.MID"));
            mydj.setHaddress(rs.getString("HADDRESS"));
            mydj.setEname(rs.getString("ENAME"));
            mydj.setHfh(rs.getString("HFH"));
            mydj.setCname(rs.getString("CNAME"));
            mydj.setCtel(rs.getString("CTEL"));
            mydj.setMdate(rs.getString("MDATE"));
            mydj.setMyj(rs.getFloat("MYJ"));
            mydj.setMyzj(rs.getFloat("MYZJ"));
            mydj.setMflag(rs.getLong("MFLAG"));
            mydj.setAllCount(count);
            mydj.setAllPage(countPage);
            mydj.setNext(nextIndex);
            mydj.setCurrent(current);
            mydj.setUp(upIndex);
            list.add(mydj);
        }
        JdbcUntils.close(rs, sta, conn);
        return list;
    }


}
